﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;

namespace Gemli.Data.Providers
{
    /// <summary>
    /// Provides an ADO.NET database provider
    /// for Gemli.Data using ANSI SQL when supplied with a
    /// <see cref="DbProviderFactory"/>. This provider
    /// class is not optimized.
    /// </summary>
    public class DbDataProvider : DataProviderBase
    {

        #region Constructor variations

        /// <summary>
        /// Constructs a DbProvider object using the
        /// specified <see cref="DbProviderFactory"/>.
        /// </summary>
        /// <param name="dbFactory"></param>
        public DbDataProvider(DbProviderFactory dbFactory)
        {
            DbFactory = dbFactory;
        }

        /// <summary>
        /// Constructs a DbProvider object using the
        /// specified <see cref="DbProviderFactory"/>.
        /// </summary>
        /// <param name="dbFactory"></param>
        /// <param name="connString"></param>
        public DbDataProvider(DbProviderFactory dbFactory, string connString)
            : this(dbFactory)
        {
            ConnectionString = connString;
        }

        /// <summary>
        /// Constructs a DbProvider object using the
        /// specified <see cref="DbProviderFactory"/>.
        /// </summary>
        /// <param name="dbFactory"></param>
        /// <param name="connStringSettings">
        /// The connection string settings in app.config or web.config as
        /// referenced by <see cref="System.Configuration.ConfigurationManager" />
        /// </param>
        public DbDataProvider(DbProviderFactory dbFactory,
            ConnectionStringSettings connStringSettings)
            : this(dbFactory, connStringSettings.Name, connStringSettings.ConnectionString)
        {
        }

        /// <summary>
        /// Constructs a DbProvider object using the
        /// specified <see cref="DbProviderFactory"/>.
        /// </summary>
        /// <param name="dbFactory"></param>
        /// <param name="connectionName"></param>
        /// <param name="connectionString"></param>
        public DbDataProvider(DbProviderFactory dbFactory,
            string connectionName, string connectionString)
            : this(dbFactory)
        {
            ConnectionName = connectionName;
            ConnectionString = connectionString;
        }
        #endregion

        /// <summary>
        /// Gets or sets the connection name associated with the <see cref="ConnectionString"/>.
        /// </summary>
        public string ConnectionName { get; set; }

        /// <summary>
        /// Returns the <see cref="DbProviderFactory"/> used for
        /// performing database operations.
        /// </summary>
        public DbProviderFactory DbFactory { get; private set; }

        /// <summary>
        /// Gets or sets the connection string used to set up
        /// a new connection to this database provider.
        /// </summary>
        public string ConnectionString
        {
            get
            {
                if (string.IsNullOrEmpty(_connString))
                {
                    if (string.IsNullOrEmpty(ConnectionName))
                    {
                        return null;
                    }
                    return (_connString = 
                            ConfigurationManager.ConnectionStrings[ConnectionName]
                                .ConnectionString);
                }
                return _connString;
            }
            set { _connString = value;}
        }
        private string _connString { get; set; }

        private DbConnection CreateAndOpenConnection()
        {
            var ret = DbFactory.CreateConnection();
            ret.ConnectionString = this.ConnectionString;
            ret.Open();
            return ret;
        }

        /// <summary>
        /// Loads the first <see cref="DataModel"/> that
        /// the specified <paramref name="query"/> finds,
        /// within the specified database <paramref name="transactionContext"/>.
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="query"></param>
        /// <param name="transactionContext"></param>
        /// <returns></returns>
        public override TModel LoadModel<TModel>(DataModelQuery<TModel> query, DbTransaction transactionContext)
        {
            var demap = DataModel.GetMapping<TModel>();

            var TOPX = "";
            if (DbFactory is System.Data.SqlClient.SqlClientFactory) TOPX = "TOP 1 ";

            var fieldList_sb = new StringBuilder();
            foreach (var fieldmap in demap.ColumnMappings)
            {
                if (fieldList_sb.Length > 0)
                {
                    fieldList_sb.Append(",");
                }
                fieldList_sb.Append("\n\t" + fieldmap.Value.ColumnName);
            }
            var fieldList = fieldList_sb.ToString();

            var orderByClause = OrderByColToExpression(typeof (TModel), query.OrderBy);

            DbConnection tmpConn = null;
            if (transactionContext == null)
            {
                tmpConn = CreateAndOpenConnection();
                transactionContext = tmpConn.BeginTransaction(IsolationLevel.ReadUncommitted);
            }
            try
            {
                var conn = transactionContext.Connection;
                using (var cmd = conn.CreateCommand())
                {
                    cmd.Transaction = transactionContext;
                    if (string.IsNullOrEmpty(demap.TableMapping.SelectProcedure))
                    {
                        var whereClause = QueryToExpression(query);
                        if (whereClause.Length > 0) whereClause += "\n";
                        cmd.CommandText = "SELECT " + TOPX + fieldList + "\n"
                                          + "FROM " + demap.TableMapping.ToString() + "\n"
                                          + whereClause
                                          + orderByClause;
                        cmd.CommandType = CommandType.Text;
                    } else
                    {
                        /* Handle scenario..
                         * 
                         * CREATE PROCEDURE sp_DataModelSelect
                         * (
                         *    @primaryKey as int,
                         *    @primaryKey2 as int
                         * ) AS
                         * BEGIN
                         *   SELECT * FROM DataModelX
                         *   WHERE  primaryKey  = @primaryKey
                         *     AND  primaryKey2 = @primaryKey2
                         * END
                         * 
                         * .. where sp_DataModelSelect is the SelectProcedure value,
                         * and @primaryKey is the SelectParam attribute property of the
                         * mapping info identified in the query conditions.
                         * 
                         */
                        cmd.CommandText = demap.TableMapping.SelectProcedure;
                        cmd.CommandType = CommandType.StoredProcedure;
                        var prms = new List<DbParameter>();
                        foreach (var condition in query.QueryConditions)
                        {
                            if (condition.CompareOp != Compare.Equal)
                                throw new InvalidOperationException(
                                    "Cannot produce an ad hoc WHERE clause with a SQL Stored Procedure.");
                            var fm = condition.FieldMap;
                            if (!string.IsNullOrEmpty(fm.SelectParam))
                            {
                                if (string.IsNullOrEmpty(fm.SelectParam)) continue;
                                var param = DbFactory.CreateParameter();
                                param.ParameterName = !fm.SelectParam.StartsWith("@")
                                                          ? "@" + fm.SelectParam
                                                          : fm.SelectParam;
                                if (param is System.Data.SqlClient.SqlParameter)
                                {
                                    var sqlparam = (System.Data.SqlClient.SqlParameter)param;
                                    sqlparam.SqlDbType = fm.SqlDbType;
                                }
                                else
                                {
                                    param.DbType = fm.DbType;
                                }
                                if (fm.DataType == typeof(string))
                                    param.Size = (condition.CompareValue ?? "").ToString().Length;
                                param.IsNullable = true;
                                param.Value = condition.CompareValue ?? DBNull.Value;
                                prms.Add(param);
                            }
                        }
                        if (prms.Count == 0)
                            throw new InvalidOperationException(
                                "No identifying filter given for single-row select.");
                        cmd.Parameters.AddRange(prms.ToArray());
                    }
                    using (var dr = cmd.ExecuteReader(CommandBehavior.SingleRow))
                    {
                        if (dr.Read())
                        {
                            var ret = (TModel) Activator.CreateInstance(typeof (TModel));
                            ret.DataProvider = this;
                            ret.Load(dr);
                            return ret;
                        }
                        return null;
                    }
                }

            } finally
            {
                if (tmpConn != null)
                {
                    transactionContext.Commit();
                    tmpConn.Close();
                }
            }
        }

        /// <summary>
        /// Loads a set of <see cref="DataModel"/> objects
        /// using the specified <paramref name="query"/> within the
        /// specified database <paramref name="transactionContext"/>.
        /// </summary>
        /// <typeparam name="TModel"></typeparam>
        /// <param name="query"></param>
        /// <param name="transactionContext"></param>
        /// <returns></returns>
        public override DataModelCollection<TModel> LoadModels<TModel>(
            DataModelQuery<TModel> query, DbTransaction transactionContext)
        {
            var demap = DataModel.GetMapping<TModel>();

            var fieldList_sb = new StringBuilder();
            foreach (var fieldmap in demap.ColumnMappings)
            {
                if (fieldList_sb.Length > 0)
                {
                    fieldList_sb.Append(",");
                }
                fieldList_sb.Append("\n\t[" + fieldmap.Value.ColumnName + "]");
            }
            var fieldList = fieldList_sb.ToString();

            var orderByClause = OrderByColToExpression(typeof(TModel), query.OrderBy);

            DbConnection tmpConn = null;
            if (transactionContext == null)
            {
                tmpConn = CreateAndOpenConnection();
                transactionContext = tmpConn.BeginTransaction(IsolationLevel.ReadUncommitted);
            }
            var conn = transactionContext.Connection;
            try
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.Transaction = transactionContext;
                    if (string.IsNullOrEmpty(demap.TableMapping.SelectManyProcedure))
                    {
                        var whereClause = QueryToExpression(query);
                        if (whereClause.Length > 0) whereClause += "\n";
                        cmd.CommandText = "SELECT " + fieldList + "\n"
                                          + "FROM " + demap.TableMapping.ToString() + "\n"
                                          + whereClause
                                          + orderByClause;
                        cmd.CommandType = CommandType.Text;
                    } else
                    {
                        /* Handle scenario..
                         * 
                         * CREATE PROCEDURE sp_DataModelSelectMany
                         * (
                         *    @filterField1 as varchar(500) = NULL,
                         *    @filterField2 as varchar(500) = NULL
                         * ) AS
                         * BEGIN
                         *   SELECT * FROM DataModelX
                         *   WHERE  (@filterField1 IS NULL OR filterField1 = @filterField1)
                         *     AND  (@filterField2 IS NULL OR filterField2 = @filterField2)
                         * END
                         * 
                         * .. where sp_DataModelSelectMany is the SelectManyProcedure value,
                         * and @primaryKey is the SelectParam attribute property of the
                         * mapping info identified in the query conditions.
                         * 
                         */
                        cmd.CommandText = demap.TableMapping.SelectManyProcedure;
                        cmd.CommandType = CommandType.StoredProcedure;
                        var prms = new List<DbParameter>();
                        foreach (var condition in query.QueryConditions)
                        {
                            if (condition.CompareOp != Compare.Equal)
                                throw new InvalidOperationException(
                                    "Cannot produce an ad hoc WHERE clause with a SQL Stored Procedure. (Limit to IsEqual().)");
                            var fm = condition.FieldMap;
                            if (!string.IsNullOrEmpty(fm.SelectManyParam))
                            {
                                if (string.IsNullOrEmpty(fm.SelectManyParam)) continue;
                                var param = DbFactory.CreateParameter();
                                param.ParameterName = !fm.SelectManyParam.StartsWith("@")
                                                          ? "@" + fm.SelectManyParam
                                                          : fm.SelectManyParam;
                                param.DbType = fm.DbType;
                                if (fm.DbType == DbType.String ||
                                    fm.DbType == DbType.StringFixedLength)
                                {
                                    param.Size = (condition.CompareValue ?? "").ToString().Length;
                                }
                                param.IsNullable = true;
                                param.Value = condition.CompareValue ?? DBNull.Value;
                                prms.Add(param);
                            }
                        }
                        if (prms.Count > 0)
                            cmd.Parameters.AddRange(prms.ToArray());
                    }
                    using (var dr = cmd.ExecuteReader())
                    {
                        var ret = new DataModelCollection<TModel>();
                        ret.DataProvider = this;
                        while (dr.Read())
                        {
                            var obj = (TModel)Activator.CreateInstance(typeof(TModel));
                            obj.DataProvider = this;
                            obj.Load(dr);
                            ret.Add(obj);
                        }
                        if (!string.IsNullOrEmpty(demap.TableMapping.SelectManyProcedure) &&
                            query.OrderBy.Count > 0)
                        {
                            // exec sort from last sort field to first to get correct end result
                            for (int i=query.OrderBy.Count-1; i>=0; i--)
                            {
                                string fld = query.OrderBy[i].GetFieldMapping(typeof (TModel)).TargetMember.Name;
                                switch (query.OrderBy[i].SortDirection)
                                {
                                    case Sort.Ascending:
                                        var asc = (from item in ret
                                                   orderby item[fld] ascending 
                                                   select item).ToArray();
                                        ret = new DataModelCollection<TModel>(asc);
                                        break;
                                    case Sort.Descending:
                                        var desc = (from item in ret
                                                   orderby item[fld] descending
                                                   select item).ToArray();
                                        ret = new DataModelCollection<TModel>(desc);
                                        break;
                                }
                            }
                        }
                        return ret;
                    }
                }
            }
            finally
            {
                if (tmpConn != null)
                {
                    transactionContext.Commit();
                    tmpConn.Close();
                }
            }
        }

        /// <summary>
        /// Saves the changes that were made to the
        /// specified <paramref name="dataModel"/>, within the specified
        /// database <paramref name="transactionContext"/>. 
        /// <remarks>
        /// The specified object must have the Deleted property set to true,
        /// IsDirty evaluated as true, or IsNew evaluated as true, or else
        /// the entity will not be saved.
        /// </remarks>
        /// </summary>
        /// <param name="dataModel"></param>
        /// <param name="transactionContext"></param>
        public override void SaveModel(DataModel dataModel, DbTransaction transactionContext)
        {
            if (!dataModel.IsNew &&
                !dataModel.IsDirty &&
                !dataModel.MarkDeleted)
                return;

            var demap = DataModelMap.GetEntityMapping(dataModel.GetType());
            string primaryKeysFilter = null;
            List<DbParameter> primaryKeysParams = null;
            if (!dataModel.IsNew)
            {
                primaryKeysParams = new List<DbParameter>();
                var primaryKeysFilter_sb = new StringBuilder();
                var pkc = demap.PrimaryKeyColumns;
                if (pkc.Length == 0)
                {
                    var lst = new List<string>();
                    foreach (var fld in demap.ColumnMappings)
                    {
                        lst.Add(fld.Value.ColumnName);
                    }
                    pkc = lst.ToArray();
                }
                foreach (var key in pkc)
                {
                    if (primaryKeysFilter_sb.Length > 0) primaryKeysFilter_sb.Append(" AND ");
                    primaryKeysFilter_sb.Append("[" + key + "]");
                    var keyMap  = new KeyValuePair<string, DataModelColumnAttribute>(null, null);
                    foreach (var field in demap.ColumnMappings)
                    {
                        var fv = field.Value;
                        if (fv.ColumnName.ToLower() == key.ToLower())
                        {
                            keyMap = field;
                            break;
                        }
                    }
                    var val = dataModel.OriginalData[keyMap.Key];
                    primaryKeysFilter_sb.Append(" = @" + key);

                    var param = DbFactory.CreateParameter();
                    param.ParameterName = "@" + keyMap.Value.ColumnName;
                    param.DbType = keyMap.Value.DbType;
                    if (keyMap.Value.DbType == DbType.String ||
                        keyMap.Value.DbType == DbType.StringFixedLength)
                    {
                        param.Size = dataModel[keyMap.Value.TargetMember.Name].ToString().Length;
                    }
                    param.IsNullable = true;
                    param.Value = val;
                    primaryKeysParams.Add(param);

                }
                primaryKeysFilter = primaryKeysFilter_sb.ToString();
            }
            DbConnection tmpConn = null;
            if (transactionContext == null)
            {
                tmpConn = CreateAndOpenConnection();
                transactionContext = tmpConn.BeginTransaction();
            }
            try
            {
                var conn = transactionContext.Connection;
                using (var cmd = conn.CreateCommand())
                {
                    cmd.Transaction = transactionContext;
                    bool wasNew = dataModel.IsNew;
                    if (dataModel.IsNew)
                    {
                        // INSERT
                        if (string.IsNullOrEmpty(demap.TableMapping.InsertProcedure))
                        {
                            var fieldNames_sb = new StringBuilder();
                            foreach (var fieldmap in demap.ColumnMappings)
                            {
                                if (fieldmap.Value.IncludeOnInsert)
                                {
                                    if (fieldNames_sb.Length > 0) fieldNames_sb.Append(",");
                                    fieldNames_sb.Append("\n\t[" + fieldmap.Value.ColumnName + "]");
                                }
                            }
                            var fieldNames = fieldNames_sb.ToString();

                            var fieldValues_sb = new StringBuilder();
                            foreach (var fieldmap in demap.ColumnMappings)
                            {
                                if (fieldmap.Value.IncludeOnInsert)
                                {
                                    var fm = fieldmap.Value;
                                    if (fieldValues_sb.Length > 0) fieldValues_sb.Append(",");
                                    fieldValues_sb.Append("\n\t@" + fm.ColumnName);
                                }
                            }
                            var fieldValues = fieldValues_sb.ToString();
                            var insertStatement = "INSERT INTO " + demap.TableMapping.ToString() + "\n"
                                                  + "("
                                                  + fieldNames + "\n"
                                                  + ") VALUES ("
                                                  + fieldValues + "\n"
                                                  + ")";
                            cmd.CommandText = insertStatement;
                            cmd.CommandType = CommandType.Text;
                            var prms = new List<DbParameter>();
                            foreach (var fieldmap in demap.ColumnMappings)
                            {
                                var fm = fieldmap.Value;
                                if (string.IsNullOrEmpty(fm.InsertParam)) continue;
                                var param = DbFactory.CreateParameter();
                                param.ParameterName = !fm.InsertParam.StartsWith("@")
                                                          ? "@" + fm.InsertParam
                                                          : fm.InsertParam;
                                param.DbType = fm.DbType;
                                if (fm.DbType == DbType.String ||
                                    fm.DbType == DbType.StringFixedLength)
                                {
                                    param.Size = dataModel[fm.TargetMember.Name].ToString().Length;
                                }
                                param.IsNullable = true;
                                if (fieldmap.Value.IncludeOnInsert)
                                {
                                    param.Value = dataModel[fm.TargetMember.Name] ?? DBNull.Value;
                                    prms.Add(param);
                                }
                                if (fieldmap.Value.ReturnOnInsert)
                                {
                                    param.Direction = ParameterDirection.Output;
                                    prms.Add(param);
                                    cmd.CommandText += "\n\n"
                                                       + "SET " + param.ParameterName 
                                                       + " = SCOPE_IDENTITY()";
                                }
                            }
                            cmd.Parameters.AddRange(prms.ToArray());
                        } else
                        {
                            /* Handle scenario..
                             * 
                             * CREATE PROCEDURE sp_DataModelInsert
                             * (
                             *    @fieldValue as varchar(500) = NULL,
                             *    @DataModel_ID as varchar(500) = NULL OUTPUT
                             * ) AS
                             * BEGIN
                             *   INSERT INTO DataModel
                             *   (
                             *      fieldValue
                             *   ) VALUES (
                             *      @fieldValue
                             *   )
                             *   
                             *   SET @DataModel_ID = SCOPE_IDENTITY()
                             * END
                             * 
                             * .. where sp_DataModelInsert is the InsertProcedure value,
                             * and @DataModel_ID is the InsertParam attribute property of the
                             * mapping info identified in the query conditions.
                             * 
                             */
                            cmd.CommandText = demap.TableMapping.InsertProcedure;
                            cmd.CommandType = CommandType.StoredProcedure;
                            var prms = new List<DbParameter>();
                            foreach (var fieldmap in demap.ColumnMappings)
                            {
                                var fm = fieldmap.Value;
                                if (string.IsNullOrEmpty(fm.InsertParam)) continue;
                                var param = DbFactory.CreateParameter();
                                param.ParameterName = !fm.InsertParam.StartsWith("@")
                                                          ? "@" + fm.InsertParam
                                                          : fm.InsertParam;
                                param.DbType = fm.DbType;
                                var val = dataModel[fm.TargetMember.Name];
                                if (fm.DbType == DbType.String ||
                                    fm.DbType == DbType.StringFixedLength)
                                {
                                    param.Size = (val ?? "").ToString().Length;
                                }
                                param.IsNullable = true;
                                if (fieldmap.Value.IncludeOnInsert)
                                {
                                    param.Value = val ?? DBNull.Value;
                                    prms.Add(param);
                                }
                                if (fieldmap.Value.ReturnOnInsert)
                                {
                                    param.Direction = ParameterDirection.Output;
                                    prms.Add(param);
                                }
                            }
                            cmd.Parameters.AddRange(prms.ToArray());
                        }
                    }
                    else if (dataModel.IsDirty && !dataModel.MarkDeleted)
                    {
                        // UPDATE
                        if (string.IsNullOrEmpty(demap.TableMapping.UpdateProcedure))
                        {
                            var fieldSet_sb = new StringBuilder();
                            var prms = new List<DbParameter>();
                            foreach (var modifiedField in dataModel.ModifiedProperties)
                            {
                                if (fieldSet_sb.Length > 0) fieldSet_sb.Append(",");
                                var fm = demap.ColumnMappings[modifiedField];
                                fieldSet_sb.Append("\n\t");
                                fieldSet_sb.Append("[" + fm.ColumnName + "]");
                                fieldSet_sb.Append(" = ");
                                fieldSet_sb.Append("@" + fm.ColumnName);

                                if (string.IsNullOrEmpty(fm.UpdateParam)) continue;
                                var param = DbFactory.CreateParameter();
                                param.ParameterName = !fm.UpdateParam.StartsWith("@")
                                                          ? "@" + fm.UpdateParam
                                                          : fm.UpdateParam;
                                param.DbType = fm.DbType;
                                var val = dataModel[fm.TargetMember.Name];
                                if (fm.DbType == DbType.String ||
                                    fm.DbType == DbType.StringFixedLength)
                                {
                                    param.Size = (val ?? "").ToString().Length;
                                }
                                param.IsNullable = true;
                                param.Value = val ?? DBNull.Value;
                                prms.Add(param);
                            }
                            cmd.Parameters.AddRange(prms.ToArray());
                            var fieldSet = fieldSet_sb.ToString();
                            var updateStatement = "UPDATE " + demap.TableMapping.ToString() + "\n"
                                                  + "SET " + fieldSet + "\n"
                                                  + "WHERE " + primaryKeysFilter;
                            cmd.CommandText = updateStatement;
                            cmd.CommandType = CommandType.Text;
                            foreach (var pkparam in primaryKeysParams)
                            {
                                if (!cmd.Parameters.Contains(pkparam.ParameterName))
                                    cmd.Parameters.Add(pkparam);
                            }
                        } else
                        {
                            /* Handle scenario..
                             * 
                             * CREATE PROCEDURE sp_DataModelUpdate
                             * (
                             *    @DataModel_ID as varchar(500),
                             *    @fieldValue as varchar(500) = NULL
                             * ) AS
                             * BEGIN
                             *   UPDATE DataModel
                             *   SET fieldValue = @fieldValue
                             *   WHERE DataModel_ID = @DataModel_ID
                             *   
                             * END
                             * 
                             * .. where sp_DataModelUpdate is the UpdateProcedure value,
                             * and @DataModel_ID is the UpdateParam attribute property of the
                             * mapping info identified in the query conditions.
                             * 
                             */
                            cmd.CommandText = demap.TableMapping.UpdateProcedure;
                            cmd.CommandType = CommandType.StoredProcedure;
                            var prms = new List<DbParameter>();
                            foreach (var fieldmap in demap.ColumnMappings)
                            {
                                var fm = fieldmap.Value;
                                if (string.IsNullOrEmpty(fm.UpdateParam)) continue;
                                var param = DbFactory.CreateParameter();
                                param.ParameterName = !fm.UpdateParam.StartsWith("@")
                                                          ? "@" + fm.UpdateParam
                                                          : fm.UpdateParam;
                                param.DbType = fm.DbType;
                                var val = dataModel[fm.TargetMember.Name];
                                if (fm.DbType == DbType.String ||
                                    fm.DbType == DbType.StringFixedLength)
                                {
                                    param.Size = (val ?? "").ToString().Length;
                                }
                                param.IsNullable = true;
                                param.Value = val ?? DBNull.Value;
                                prms.Add(param);
                            }
                            cmd.Parameters.AddRange(prms.ToArray());

                        }
                    }
                    else if (dataModel.MarkDeleted)
                    {
                        // DELETE
                        if (string.IsNullOrEmpty(demap.TableMapping.DeleteProcedure))
                        {
                            var deleteStatement = "DELETE FROM " + demap.TableMapping.ToString() + "\n"
                                                  + "WHERE " + primaryKeysFilter;
                            cmd.CommandText = deleteStatement;
                            cmd.CommandType = CommandType.Text;
                            foreach (var pkparam in primaryKeysParams)
                            {
                                if (!cmd.Parameters.Contains(pkparam.ParameterName))
                                    cmd.Parameters.Add(pkparam);
                            }
                        } else
                        {
                        /* Handle scenario..
                         * 
                         * CREATE PROCEDURE sp_DataModelDelete
                         * (
                         *    @DataModel_ID as varchar(500)
                         * ) AS
                         * BEGIN
                         *   DELETE FROM DataModel
                         *   WHERE DataModel_ID = @DataModel_ID
                         *   
                         * END
                         * 
                         * .. where sp_DataModelDelete is the DeleteProcedure value,
                         * and @DataModel_ID is the UpdateParam attribute property of the
                         * mapping info identified in the query conditions.
                         * 
                         */
                            cmd.CommandText = demap.TableMapping.DeleteProcedure;
                            cmd.CommandType = CommandType.StoredProcedure;
                            foreach (var pkparam in primaryKeysParams)
                            {
                                if (!cmd.Parameters.Contains(pkparam.ParameterName))
                                    cmd.Parameters.Add(pkparam);
                            }

                        }
                    }
                    cmd.ExecuteNonQuery();
                    if (wasNew)
                    {
                        foreach (var field in demap.ColumnMappings)
                        {
                            var f = field.Value;
                            if (f.ReturnOnInsert)
                            {
                                dataModel[f.TargetMember.Name] = cmd.Parameters["@" + f.ColumnName].Value;
                            }
                        }
                    }
                }
            } finally
            {
                if (tmpConn != null)
                {
                    transactionContext.Commit();
                    tmpConn.Close();
                }
            }
        }

        /// <summary>
        /// Saves the changes that were made to each of the
        /// specified <paramref name="dataEntities"/>, within the specified
        /// database <paramref name="transactionContext"/>. 
        /// <remarks>
        /// The specified entities must have the Deleted property set to true,
        /// IsDirty evaluated as true, or IsNew evaluated as true, or else
        /// the entity will not be saved.
        /// </remarks>
        /// </summary>
        /// <param name="dataEntities"></param>
        /// <param name="transactionContext"></param>
        public override void SaveModels<TModel>(DataModelCollection<TModel> dataEntities, DbTransaction transactionContext)
        {

            DbConnection tmpConn = null;
            if (transactionContext == null)
            {
                tmpConn = CreateAndOpenConnection();
                transactionContext = tmpConn.BeginTransaction();
            }
            try
            {
                foreach (var entity in dataEntities)
                {
                    SaveModel(entity, transactionContext);
                }
            } finally
            {
                if (tmpConn != null)
                {
                    transactionContext.Commit();
                    tmpConn.Close();
                }
            }
        }

        /// <summary>
        /// Returns true, indicating that this data provider
        /// supports creating and working with DbTransactions.
        /// </summary>
        public override bool SupportsTransactions
        {
            get { return true; }
        }

        /// <summary>
        /// Starts a database transaction.
        /// </summary>
        /// <returns></returns>
        public override DbTransaction BeginTransaction()
        {
            return this.CreateAndOpenConnection().BeginTransaction();
        }

        /// <summary>
        /// Starts a database transaction with the specified isolation level.
        /// </summary>
        /// <param name="isolationLevel"></param>
        /// <returns></returns>
        public override DbTransaction BeginTransaction(IsolationLevel isolationLevel)
        {
            return this.CreateAndOpenConnection().BeginTransaction(isolationLevel);
        }

        private string QueryToExpression(DataModelQuery query)
        {
            if (!string.IsNullOrEmpty(query.RawExpression)) return query.RawExpression;
            Type t = typeof(DataModel);
            if (query.GetType().IsGenericType)
            {
                t = query.GetType().GetGenericArguments()[0];
            }
            var map = DataModelMap.GetEntityMapping(t);
            var sb = new StringBuilder();
            foreach (var condition in query.QueryConditions)
            {
                if (condition.FieldMappingKey != null &&
                    condition.FieldMap == null)
                {
                    switch (condition.FindFieldMappingBy)
                    {
                        case FieldMappingKeyType.ClrMember:
                            condition.FieldMap = map.ColumnMappings[condition.FieldMappingKey];
                            break;
                        case FieldMappingKeyType.DbColumn:
                            condition.FieldMap = map.GetFieldMappingByDbColumnName(condition.FieldMappingKey);
                            break;
                    }
                }
                if (condition.FieldMap != null)
                {
                    if (sb.Length > 0) sb.Append(" AND ");
                    sb.Append(condition.FieldMap.ColumnName);
                    sb.Append(CompareOpToExpression(condition.CompareOp));
                    sb.Append(ValueToExpression(condition.FieldMap, condition.CompareValue));
                }
                else
                {
                    if (sb.Length > 0) sb.Append(" AND ");
                    sb.Append(condition.FieldMappingKey);
                    sb.Append(CompareOpToExpression(condition.CompareOp));
                    if (condition.CompareOp != Compare.Null &&
                        condition.CompareOp != Compare.NotNull)
                    {
                        sb.Append(ValueToExpression(
                            DbTypeConverter.ToDbType(condition.CompareValue.GetType()),
                            condition.CompareValue));
                    }
                }
            }
            return sb.Length == 0 ? string.Empty : "WHERE " + sb.ToString();
        }

        private string ValueToExpression(DbType dbType, object value)
        {
            if (value == null || value == DBNull.Value) return "NULL";
            var sb = new StringBuilder();
            bool apos = false;
            switch (dbType)
            {
                case DbType.AnsiString:
                case DbType.AnsiStringFixedLength:
                case DbType.Date:
                case DbType.DateTime:
                case DbType.DateTimeOffset:
                case DbType.Guid:
                case DbType.String:
                case DbType.StringFixedLength:
                case DbType.Time:
                case DbType.Xml:
                    apos = true;
                    sb.Append("'");
                    break;
            }
            sb.Append(value.ToString().Replace("'", "''"));
            if (apos) sb.Append("'");
            return sb.ToString();
        }

        private string ValueToExpression(DataModelColumnAttribute fieldMetadata, object value)
        {
            return ValueToExpression(fieldMetadata.DbType, value);
        }

        private string CompareOpToExpression(Compare compare)
        {
            switch (compare)
            {
                case Compare.Equal:
                    return " = ";
                case Compare.NotEqual:
                    return " != ";
                case Compare.GreaterThan:
                    return " > ";
                case Compare.GreaterThanOrEqual:
                    return " >= ";
                case Compare.LessThan:
                    return " < ";
                case Compare.LessThanOrEqual:
                    return " <= ";
                case Compare.Like:
                    return " LIKE ";
                case Compare.Null:
                    return " IS NULL";
                case Compare.NotNull:
                    return " IS NOT NULL";
            }
            throw new NotImplementedException("Enum value not handled: " + compare);
        }

        private string OrderByColToExpression(Type context, List<DataModelQuery.SortItem> orderByCollection)
        {
            if (orderByCollection == null) return string.Empty;
            var sb = new StringBuilder();
            foreach (var item in orderByCollection)
            {
                if (sb.Length > 0) sb.Append(", ");
                sb.Append(item.GetFieldMapping(context).ColumnName);
                switch (item.SortDirection)
                {
                    case Sort.Ascending:
                        sb.Append(" ASC");
                        break;
                    case Sort.Descending:
                        sb.Append(" DESC");
                        break;
                }
            }
            if (sb.Length > 0) return "ORDER BY " + sb;
            return "";
        }
    }
}
